--exec sp_search_assembly_release -1, -1, 2008,'-1','-1', 'PROD'

ALTER PROC sp_search_assembly_release( @processStep_id INT, @vendor_id INT, @year INT, @status VARCHAR(50), @releaseNumber varchar(50), @search_release_type varchar(50) )
AS
BEGIN
	DECLARE @results TABLE ( AssemblyReleaseID INT, Assembly_ReleaseNo VARCHAR(50), Part_Number VARCHAR(50), Vendor_Name VARCHAR(50), Release_Date VARCHAR(50), Status VARCHAR(50),  QTY_PULLED INT, Request_Type VARCHAR(50), Turn_Key_Label VARCHAR(50), IS_PARTIAL_RECEIVE INT )
 	DECLARE @SQLString NVARCHAR(3000)
	DECLARE @ParamDefinition NVARCHAR(500)
	DECLARE @AssemblyReleaseId INT
	DECLARE @AssemblyReleaseNo VARCHAR(50)
	DECLARE @ReleaseDate VARCHAR(10)
	DECLARE @PartNumber VARCHAR(50)
	DECLARE @VendorName VARCHAR(50)
	DECLARE @ReleaseStatus VARCHAR(50)
	DECLARE @Release_Type VARCHAR(50)
	DECLARE @Turn_Key_Label VARCHAR(50)
	DECLARE @AssemblyReleaseByPartNumber VARCHAR(50)
	DECLARE @QTY_PULLED INT

	DECLARE @IS_TEST		BIT
	DECLARE @IS_TAPE_REEL	BIT
	DECLARE @IS_TURN_KEY	BIT
	DECLARE @TRANSFER_LOT_COUNT INT
	DECLARE @IS_PARTIAL_RECEIVE INT
	DECLARE @SHIPPED_LOT_COUNT INT

	SET @SQLString = N'declare releaseCursor CURSOR for select Assembly_release_Id, Assembly_Release_No, Convert( VARCHAR, Assembly_release_date, 101), Part_Number, V_name, Status, Request_Type from Assm_release_table, Product, Vendor where Assm_release_table.ProcessStep_ID = Product.ProcessStep_ID AND Assm_release_table.TO_Vendor_ID = Vendor.Vendor_ID '
	SET @ParamDefinition = N'@Prm_ProcessStep_Id INT, @Prm_Vendor_Id INT, @Prm_Status VARCHAR(50), @Prm_Year INT, @Prm_Release_No VARCHAR(50), @Prm_Release_Type VARCHAR(50)'

	IF @processStep_id  <> -1
	BEGIN
		SELECT @AssemblyReleaseByPartNumber = PROPERTY_VALUE FROM CONFIGURATION_TABLE WHERE Property_Key = 'scalar.chiptrack.operations.assemblyrelease.byFGorAssemblyPart'
		IF( @AssemblyReleaseByPartNumber = 'Assembly' )
		BEGIN
			SET @SQLString = @SQLString + ' AND Assm_release_table.ProcessStep_ID IN ( @Prm_ProcessStep_Id ) '
		END
		ELSE
		BEGIN
			SET @SQLString = @SQLString + ' AND Assm_release_table.ProcessStep_ID IN ( Select Processstep_Id FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @Prm_ProcessStep_Id, 0 ) WHERE PS_TYPE = ''Assembly'' )  '
		END
	END

	IF @vendor_id  <> -1
	BEGIN
		SET @SQLString = @SQLString + ' AND Assm_release_table.TO_Vendor_ID = @Prm_Vendor_Id '
	END

	IF @status  <> '-1'
	BEGIN
		SET @SQLString = @SQLString + ' AND Assm_release_table.Status = @Prm_Status '
	END

	IF @year  <> -1
	BEGIN
		SET @SQLString = @SQLString + ' AND YEAR( Assembly_release_date ) = @Prm_Year '
	END

	IF @releaseNumber  <> '-1'
	BEGIN
		SET @SQLString = @SQLString + ' AND Assm_release_table.Assembly_Release_No = @Prm_Release_No '
	END

	IF @search_release_type  <> '-1'
	BEGIN
		SET @SQLString = @SQLString + ' AND Assm_release_table.Request_Type = @Prm_Release_Type '
	END


 	SET @SQLString = @SQLString + ' ORDER BY CONVERT(DATETIME,Assembly_release_date,101) DESC, Assembly_release_Id DESC '	
	execute sp_executesql @SQLString, @ParamDefinition, @Prm_ProcessStep_Id = @processStep_id, @Prm_Vendor_Id = @vendor_id, @Prm_Status = @status, @Prm_Year = @year, @Prm_Release_No = @releaseNumber, @Prm_Release_Type = @search_release_type  

 	OPEN releaseCursor 
  	FETCH NEXT FROM releaseCursor INTO  @AssemblyReleaseId, @AssemblyReleaseNo, @ReleaseDate, @PartNumber, @VendorName, @ReleaseStatus, @Release_Type
  	WHILE @@fetch_status = 0
  	BEGIN
  	    SELECT @QTY_PULLED = ISNULL( SUM( QTY_PULLED ), 0 ) FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @AssemblyReleaseId
			
		SELECT @IS_TURN_KEY = TURN_KEY, @IS_TEST = Is_Test, @IS_TAPE_REEL = Is_Tape_Reel FROM ASSM_RELEASE_TABLE WHERE ASSEMBLY_RELEASE_ID = @AssemblyReleaseId
		
		IF( @IS_TURN_KEY = 0 AND @IS_TEST = 0 AND @IS_TAPE_REEL = 0 )
        BEGIN
            SET @Turn_Key_Label = 'Assm+Test+T&R'
        END
        ELSE IF( @IS_TURN_KEY = 0 AND @IS_TEST = 0 AND @IS_TAPE_REEL = 1 )
        BEGIN
			SET @Turn_Key_Label = 'Assm+Test'
        END
        ELSE
        BEGIN
            SET @Turn_Key_Label = 'Assembly'
        END
		
		SELECT @TRANSFER_LOT_COUNT = COUNT(*) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P 
				WHERE LT.LOT_ID = SL.LOT_ID  AND SL.STATUS = 'Close'
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'Die Bank'
				AND SL.SL_NEXT_PROCESS <> 'DIE BANK' 
				AND LT.LOT_NUMBER IN (SELECT DISTINCT SHIPPED_LOT_NUMBER FROM ASSM_RELEASE_TABLE ART, Assm_Release_Lot_Table ARLT 
						WHERE ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID
							AND ART.STATUS Not In ('Cancelled' , 'Closed')
							AND ART.ASSEMBLY_RELEASE_ID = @AssemblyReleaseId )
		
		/*SELECT @SHIPPED_LOT_COUNT = COUNt(DISTINCT SHIPPED_LOT_NUMBER) FROM ASSM_RELEASE_TABLE ART, Assm_Release_Lot_Table ARLT 
						WHERE ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID
							AND ART.STATUS Not In ('Cancelled' , 'Closed')
							AND ART.ASSEMBLY_RELEASE_ID = @AssemblyReleaseId
		PRINT @SHIPPED_LOT_COUNT
		PRINT @TRANSFER_LOT_COUNT
		IF @SHIPPED_LOT_COUNT = @TRANSFER_LOT_COUNT
		BEGIN
				UPDATE ASSM_RELEASE_TABLE SET Status = 'Closed' WHERE ASSEMBLY_RELEASE_ID = @AssemblyReleaseId AND STATUS Not In ('Cancelled' , 'Closed')
		END */

		IF @TRANSFER_LOT_COUNT > 0
		BEGIN
				SET @IS_PARTIAL_RECEIVE = 0
		END
		ELSE
		BEGIN
				SET @IS_PARTIAL_RECEIVE = 1	
		END
	
		INSERT INTO @results( AssemblyReleaseID, Assembly_ReleaseNo, Part_Number, Vendor_Name, Release_Date, Status, QTY_PULLED, Request_Type, Turn_Key_Label, IS_PARTIAL_RECEIVE ) VALUES ( @AssemblyReleaseId, @AssemblyReleaseNo, @PartNumber, @VendorName, @ReleaseDate, @ReleaseStatus, @QTY_PULLED, @Release_Type, @Turn_Key_Label, @IS_PARTIAL_RECEIVE )

		SET @IS_TURN_KEY = 1
		SET @IS_TEST = 1
		SET @IS_TAPE_REEL = 1
		SET @Turn_Key_Label = 'Assembly'
		SET @IS_PARTIAL_RECEIVE = 1
		SET @TRANSFER_LOT_COUNT = 0
		SET @SHIPPED_LOT_COUNT  = 0
		FETCH NEXT FROM releaseCursor INTO  @AssemblyReleaseId, @AssemblyReleaseNo, @ReleaseDate, @PartNumber, @VendorName, @ReleaseStatus, @Release_Type
	END
	CLOSE releaseCursor 
	DEALLOCATE releaseCursor  
	SELECT * FROM  @results
END